UFCFEL-15-3 Security Data Analytics and Visualisation¶

# PLEASE ENTER THE NAME OF THE DATASET ASSIGNED TO YOU AS INDICATED ON BLACKBOARD

DATASET = 'uwetech-dataset04'

import random

import string

import pandas as pd

import matplotlib.pyplot as plt

import seaborn

import datetime

​

def load_data(DATASET):

    dataset_list = ['uwetech-dataset01', 'uwetech-dataset02', 'uwetech-dataset03', 'uwetech-dataset04']

    if DATASET in dataset_list:

        email_data = pd.read_csv('./' + DATASET + '/email_data.csv', parse_dates=True, index_col=0)

        file_data = pd.read_csv('./' + DATASET + '/file_data.csv', parse_dates=True, index_col=0)

        web_data = pd.read_csv('./' + DATASET + '/web_data.csv', parse_dates=True, index_col=0)

        login_data = pd.read_csv('./' + DATASET + '/login_data.csv', parse_dates=True, index_col=0)

        usb_data = pd.read_csv('./' + DATASET + '/usb_data.csv', parse_dates=True, index_col=0)

        employee_data = pd.read_csv('./' + DATASET + '/employee_data.csv', index_col=0)

        email_data['datetime'] = pd.to_datetime(email_data['datetime'])

        file_data['datetime'] = pd.to_datetime(file_data['datetime'])

        web_data['datetime'] = pd.to_datetime(web_data['datetime'])

        login_data['datetime'] = pd.to_datetime(login_data['datetime'])

        usb_data['datetime'] = pd.to_datetime(usb_data['datetime'])

    else:

        print ("DATASET variable not defined - please refer to Blackboard for DATASET name")

        return

    return employee_data, login_data, usb_data, web_data, file_data, email_data

​

employee_data, login_data, usb_data, web_data, file_data, email_data = load_data(DATASET)

# This shows the employee_data DataFrame

employee_data

# This shows the login_data DataFrame

login_data

# This shows how to filter the login_data DataFrame by a particular username

login_data[login_data['user']=='usr-sfo']

# This shows how to filter the login_data DataFrame by a particular set of usernames within a list

login_data[login_data['user'].isin(['usr-sfo'])]

# all_roles is an array/list of all job roles that are within our DataFrame

all_roles = employee_data['role'].unique()

all_roles

### This sample code helps to create two dictionary objects - user_set and user_set_emails - that group usernames and emails by job role.

​

user_set = {}

user_set_emails = {}

all_roles = employee_data['role'].unique()

for role in all_roles:

    user_set[role] = list(employee_data[employee_data['role'] == role]['user'].values)

    user_set_emails[role] = list(employee_data[employee_data['role'] == role]['email'].values)

# List all usernames that belong to the job role Finance

user_set['Finance']

######### ADD YOUR CODE HERE ##########

​

#data in January

january_data_finance = login_data[login_data['datetime'].dt.month == 1 & login_data['user'].isin(user_set['Finance'])]

​

#dataframe for January data

# january_data_finance

​

#number of login and logoff that occurs in each hour of the day

hour_count = january_data_finance.groupby(['action',january_data_finance['datetime'].dt.hour]).size().unstack().fillna(0)

​

#plot bar chart of login/logoff january finance users 

hour_count.plot(kind='bar')

plt.title('Logon and Logoff Distribution Of January Finance Users')

plt.xlabel('Hours')

plt.ylabel('Count')

​

plt.show()

######### ADD YOUR CODE HERE ##########

plt.figure(figsize=(20,10))

​

#getting the dataframe for january

january_data = login_data[login_data['datetime'].dt.month == 1]

​

#filtering data for only PC42

user_pc42 = january_data[january_data['pc'] == 'pc42']

login_pc42 = user_pc42[user_pc42['action']== 'login'].copy()

logoff_pc42 = user_pc42[user_pc42['action']== 'logoff'].copy()

​

​

#extract time from dataframe and covert it to string

login_pc42['time'] = login_pc42['datetime'].dt.time.astype(str)

logoff_pc42['time'] = logoff_pc42['datetime'].dt.time.astype(str)

​

#sort time (y) axis for better readability 

login_pc42_time = login_pc42.sort_values(by = "time")

logoff_pc42_time = logoff_pc42.sort_values(by = "time")

​

#extract the date from dataframe

login_pc42['date'] = login_pc42['datetime'].dt.date

logoff_pc42['date'] = logoff_pc42['datetime'].dt.date

​

​

plt.plot(login_pc42['date'],login_pc42_time['time'],label='login')

plt.plot(logoff_pc42['date'],logoff_pc42_time['time'],label='logoff')

​

#customize the plot

plt.title('Logon and Logoff Times for pc42 in January')

plt.xlabel('Date')

plt.ylabel('Activity Time')

plt.legend()

​

#show the plot

plt.show()

######### ADD YOUR CODE HERE ##########

import networkx as nx

plt.figure(figsize=(20,25))

​

#filter the users email of security employee

security_send = user_set_emails['Security']

​

#match the emails of security employee with email dataframe

sec_sent_emails = email_data[email_data['sender'].isin(security_send)]

​

#filter the email of security employee sent in january 5th

jan_sec_emails = sec_sent_emails[sec_sent_emails['datetime'].dt.floor('D') == '2022-01-05']

​

data = jan_sec_emails

​

#node-link graph 

G = nx.from_pandas_edgelist(data, 'sender', 'recipient')

x = nx.spring_layout(G)

nx.draw(G, x, with_labels=True, node_size=500, font_size=14, font_color='black',

        node_color='cyan', edge_color="grey", width=2, alpha=0.8)

​

plt.title("Node-Link Diagram for Security Staff Emails on January 5th 2022")

plt.show()

######### ADD YOUR CODE HERE ##########

import networkx as nx

import matplotlib.pyplot as plt

​

#get list of all users

all_users = list(set(email_data['sender'].unique()) | set(email_data['recipient'].unique()))

​

# security email for january 5th

data = jan_sec_emails

​

#create a graph with a shell layout

G = nx.Graph()

G.add_nodes_from(all_users)

​

edges = list(zip(data['sender'], data['recipient']))

G.add_edges_from(edges)

​

shell_lay = [security_send, all_users]

ps = nx.shell_layout(G, nlist=shell_lay)

​

#nodelink graph

plt.figure(figsize=(20, 25))

nx.draw(G, ps, with_labels=True, node_size=500, font_size=14, node_color='cyan', edge_color="grey", width=2, alpha=0.8)

​

plt.title("Node-Link Diagram for Security Staff Emails on January 5th 2022 for All Users")

plt.show()

​

######### ADD YOUR CODE HERE ##########

​

#filter files for january 

jan_file_data = file_data[file_data['datetime'].dt.month == 1].copy()

​

# jan_file_data

​

​

#combine files dataframe with users job lists into one df

jan_user_role = dict(zip(employee_data['user'], employee_data['role']))

​

# Add 'role' column to file_data_copy using the mapping

jan_file_data['role'] = jan_file_data['user'].map(jan_user_role)

​

#group by the 'role' and 'filename' columns

group_data_jan = jan_file_data.groupby(['role', 'filename']).size().reset_index(name='count')

​

group_data_jan

​

#create a dataframe suitable for plotting

pivot_jan = group_data_jan.pivot(index='filename', columns='role', values='count').fillna(0)

​

#filter columns

pivot_jan = pivot_jan[['HR', 'Security', 'Services']]

​

# pivot_jan

​

#plot the data in stacked bar chart 

ax = pivot_jan.plot(kind='bar', width=0.8)

​

# Add labels and title

plt.title('Files Accessed by HR, Services, and Security Staff for January')

plt.xlabel('File Accessed')

plt.ylabel('Number of Accesses')

plt.legend(title='Role')

​

#add legend

ax.legend(title='Role', bbox_to_anchor=(1.05, 1), loc='upper left')

plt.show()

#check if there are no missing values in all data files

print('login data')

print(login_data.isnull().sum())

print('email data')

print(email_data.isnull().sum())

print('employee data')

print(employee_data.isnull().sum())

print('usb data')

print(usb_data.isnull().sum())

print('file data')

print(file_data.isnull().sum())

print('web data')

print(web_data.isnull().sum())

#make a copy of the data files in order to not mess the original one

login_data_copy = login_data.copy()

file_data_copy = file_data.copy()

employee_data_copy = employee_data.copy()

usb_data_copy = usb_data.copy()

web_data_copy = web_data.copy()

email_data_copy = email_data.copy()

import seaborn as sns

#login logoff filter for action

login_events = login_data_copy[login_data_copy['action'].isin(['login', 'logoff'])]

​

#extract only hour 

login_events['hour'] = login_events['datetime'].dt.hour

​

#bar chart for login and logoff distribution over time

plt.figure(figsize=(12, 6))

sns.countplot(x='hour', hue='action', data=login_events)

plt.title('Distribution of Login and Logoff Times by Hour')

plt.xlabel('Hour')

plt.ylabel('Count')

plt.legend(title='Action')

plt.show()

# Count the number of actions per user per day

usr_actions_per_day = login_data_copy.groupby(['user', login_data_copy['datetime'].dt.date])['action'].count().reset_index()

usr_more_two_actions = usr_actions_per_day[usr_actions_per_day['action'] > 2]['user'].unique()

#users with more than 2 actions in a day

usr_more_two_actions

######### ADD YOUR CODE HERE ##########

employee_data_copy[employee_data_copy['user']=='usr-yqu']

#remove usr-yqu from copy of data files

login_data_copy = login_data_copy[login_data_copy['user']!='usr-yqu']

file_data_copy = file_data_copy[file_data_copy['user']!='usr-yqu']

employee_data_copy = employee_data_copy[employee_data_copy['user']!='usr-yqu']

usb_data_copy = usb_data_copy[usb_data_copy['user']!='usr-yqu']

web_data_copy = web_data_copy[web_data_copy['user']!='usr-yqu']

email_data_copy = email_data_copy[email_data_copy['sender']!='usr-yqu@uwetech.com']

#distribution of employee roles

plt.figure(figsize=(10, 6))

sns.countplot(x='role', data=employee_data_copy, palette='Set2')

plt.title('Distribution of Employee Roles')

plt.xlabel('Role')

plt.ylabel('Count')

plt.show()

user_role_mapping = dict(zip(employee_data_copy['user'], employee_data_copy['role']))

​

# Add 'role' column to file_data_copy using the mapping

file_data_copy['role'] = file_data_copy['user'].map(user_role_mapping)

​

# Group by the 'role' and 'filename' columns

grouped_data = file_data_copy.groupby(['role', 'filename']).size().reset_index(name='count')

​

# Create a bar chart

plt.figure(figsize=(15, 8))

sns.barplot(x='filename', y='count', hue='role', data=grouped_data)

plt.title('Count of Files Accessed by Users from Different Roles')

plt.xlabel('Role')

plt.ylabel('Count')

plt.xticks(rotation=45)

plt.legend(title='Filename', bbox_to_anchor=(1.05, 1), loc='upper left')

plt.show()

#create a dictionary mapping user to role

user_role_mapping = dict(zip(employee_data_copy['user'], employee_data_copy['role']))

​

#add 'role' column to file_data_copy using the mapping

file_data_copy['role'] = file_data_copy['user'].map(user_role_mapping)

​

#group the 'filename' and 'role' columns

grouped_data = file_data_copy.groupby(['user', 'filename', 'role']).size().reset_index(name='count')

​

#files accessed by only one user per role

files_accessed_by_one_person = grouped_data[grouped_data['count'] == 1]

#sort file data based on 'role'

sorted_data = file_data_copy.sort_values(by='role')

​

#group data by 'role' and 'filename', whithin unique users

grouped = sorted_data.groupby(['role', 'filename'])['user'].nunique().reset_index()

​

#filter files accessed by one user

files_accessed_by_one_person = grouped[grouped['user'] == 1]

​

#create an empty list for filtered results

filtered_results = []

​

#loop through the rows accessed files

for index, row in files_accessed_by_one_person.iterrows():

    role = row['role']

    filename = row['filename']

    #get the single user and datetime values

    single_user = sorted_data[(sorted_data['role'] == role) & (sorted_data['filename'] == filename)]['user'].iloc[0]

    datetime_value = sorted_data[(sorted_data['role'] == role) & (sorted_data['filename'] == filename)]['datetime'].iloc[0]

    # Append the results

    filtered_results.append({'datetime': datetime_value,'user': single_user,'role':role,'filename': filename})

#create a df 

file_result_df = pd.DataFrame(filtered_results)

​

file_result_df

employee_data[employee_data['user']=='usr-ezr']

usr_ezr_data = usb_data_copy[usb_data_copy['user'] == 'usr-ezr']

​

​

​

usb_insert_data = usr_ezr_data[usr_ezr_data['action'] == 'usb_insert']

usb_remove_data = usr_ezr_data[usr_ezr_data['action'] == 'usb_remove']

​

#scatter plot usb action for usr-ezr

plt.figure(figsize=(15, 8))

plt.scatter(usb_insert_data['datetime'], usb_insert_data['pc'], label='usb_insert', marker='o',s=100)

​

plt.scatter(usb_remove_data['datetime'], usb_remove_data['pc'], label='usb_remove', marker='o')

​

plt.xlabel('Date and Time')

plt.ylabel('PC')

plt.title('USB Actions for usr-ezr (Scatter Plot)')

plt.legend()

plt.show()

employee_data_copy[employee_data_copy['pc']=='pc249']

usb_data_copy[usb_data_copy['user']=='usr-ezr']

# #number of each USB action

# usb_action_counts = pc_249_data['user'].value_counts()

# plt.figure(figsize=(12, 8))

# usb_action_counts.plot(kind='bar', color='green')

# plt.xlabel('User')

# plt.ylabel('Number of Occurrences')

# plt.title('USB Actions for PC249 by User')

# plt.show()

# Convert 'datetime' column to datetime type

login_data_copy['datetime'] = pd.to_datetime(login_data_copy['datetime'])

​

# Filter data for PC with identifier 'pc249'

pc_249_data = login_data_copy[login_data_copy['pc'] == 'pc249']

​

# Count the number of each login action

login_action_counts = pc_249_data.groupby(['datetime', 'user', 'action']).size().reset_index(name='count')

​

# Plot bar chart with datetime as x-axis

fig, ax = plt.subplots()

login_action_counts.set_index('datetime').groupby('user')['action'].value_counts().unstack().plot(kind='bar', ax=ax, stacked=True)

plt.title('Login Actions for PC249')

plt.show()

#get start date and end date to check the login

# activity during the time the usb from usr-ezr was inserted

start_date = pd.to_datetime('2022-05-11')

end_date = pd.to_datetime('2022-05-21')

​

# get login data for usr-xfr and actions between 11-05-2022 and 21-05-2022

xfr_login_data = login_data_copy[(login_data_copy['pc'] == 'pc249') & 

                                  (login_data_copy['datetime'] >= start_date) & 

                                  (login_data_copy['datetime'] <= end_date)]

xfr_logout_data = xfr_login_data[xfr_login_data['action'] == 'logoff']

​

# Create line plot for both login and logoff

plt.figure(figsize=(15, 8))

plt.plot(xfr_login_data['datetime'], xfr_login_data['datetime'].dt.hour, label='login', marker='o')

​

​

plt.plot(xfr_logout_data['datetime'], xfr_logout_data['datetime'].dt.hour, label='logoff', marker='o')

​

plt.xlabel('Date')

plt.ylabel('Time')

plt.title('Login Actions for usr-xfr (12-05-2022 to 20-05-2022)')

plt.legend()

plt.show()

​

#get start date and end date to check the login

# activity during the time the usb from usr-ezr was inserted

start_date = pd.to_datetime('2022-05-11')

end_date = pd.to_datetime('2022-05-21')

​

#get login data for usr-ezr and actions between 11-05-2022 and 21-05-2022

ezr_usb_inser = usb_data_copy[(usb_data_copy['user'] == 'usr-ezr') & 

                                  (usb_data_copy['datetime'] >= start_date) & 

                                  (usb_data_copy['datetime'] <= end_date)]

ezr_usb_rem = ezr_usb_inser[ezr_usb_inser['action'] == 'usb_remove']

​

#line plot for login and logoff

plt.figure(figsize=(5, 5))

plt.scatter(ezr_usb_inser['datetime'], ezr_usb_inser['datetime'].dt.hour, label='insert', marker='o')

​

plt.scatter(ezr_usb_rem['datetime'], ezr_usb_rem['datetime'].dt.hour, label='remove', marker='o')

​

plt.xlabel('Date')

plt.ylabel('Time')

plt.title('usb action for usr-ezr in pc249 (11-05-2022 to 21-05-2022)')

plt.xticks(rotation=45)

plt.legend()

plt.show()

​

user_ezr_data = web_data_copy[web_data_copy['user'] == 'usr-ezr']

​

#number of accesses for each website

website_counts = user_ezr_data['website'].value_counts()

​

​

website_counts.plot(kind='bar')

plt.xlabel('Website')

plt.ylabel('Number of Accesses')

plt.xticks(rotation=45, ha='right') 

plt.title('Number of Website Accessed by usr-ezr')

plt.show()

​

#this code unable to create two dictionaries to sort pc with website accessed 

user_web = {}

all_pc = employee_data_copy['pc'].unique()

​

for pc in all_pc:

    #users for the current pc

    pc_users = employee_data_copy[employee_data_copy['pc'] == pc]['user'].values

    #websites accessed by users

    websites_for_pc = web_data_copy[web_data_copy['user'].isin(pc_users)]['website'].unique()

    # save in dictionary

    user_web[pc] = list(websites_for_pc)

import networkx as nx

pc1 = 'pc35'

pc2 = 'pc249'

​

#list of websites accessed by each PC

websites_pc1 = set(user_web.get(pc1, []))

websites_pc2 = set(user_web.get(pc2, []))

​

#common websites accessed by both PCs

common_websites = websites_pc1.intersection(websites_pc2)

uncommon_websites_pc1 = websites_pc1 - common_websites

uncommon_websites_pc2 = websites_pc2 - common_websites

​

#ceate graph

G = nx.DiGraph()

​

#nodes for each PC and websites

G.add_node(pc1, color='blue')

G.add_node(pc2, color='red')

​

for website in common_websites:

    G.add_node(website, color='green')

#node for uncommon websites

for website in uncommon_websites_pc1:

    G.add_node(website, color='orange')

​

for website in uncommon_websites_pc2:

    G.add_node(website, color='purple')

#edges connecting PCs to common websites

for website in common_websites:

    G.add_edge(pc1, website)

    G.add_edge(pc2, website)

​

for website in uncommon_websites_pc1:

    G.add_edge(pc1, website)

​

for website in uncommon_websites_pc2:

    G.add_edge(pc2, website)

​

​

pos = nx.spring_layout(G)

node_colors = [G.nodes[node]['color'] for node in G.nodes]

​

nx.draw(G, pos, with_labels=True, node_size=700, node_color=node_colors, font_size=10, font_color='black', edge_color="grey", width=1, alpha=1)

​

plt.title(f'Websites Accessed by {pc1} and {pc2}')

plt.show()

website_counts = web_data_copy['website'].value_counts()

plt.figure(figsize=(12, 8))

sns.barplot(x=website_counts.index, y=website_counts.values, palette='viridis')

plt.xticks(rotation=45, ha='right')

plt.xlabel('Website')

plt.ylabel('Number of Accesses')

plt.title('Website Access Counts')

plt.show()

plt.figure(figsize=(20, 25))

​

#filter emails sent by 'usr-ezr@uwetech.com'

usr_ezr_sent = email_data_copy[email_data_copy['sender'] == 'usr-ezr@uwetech.com']

​

#match emails of 'usr-ezr@uwetech.com' with the email_data

sent_emails = email_data_copy[email_data_copy['sender'].isin(usr_ezr_sent['sender'])]

​

#emails sent by 'usr-ezr@uwetech.com' in a specific month let's do january

ezr_emails = sent_emails[sent_emails['datetime'].dt.month == 5]

​

#node-link graph

G = nx.from_pandas_edgelist(ezr_emails, 'sender', 'recipient')

x = nx.spring_layout(G)

​

#assign different colors when email @domain is different 

node_colors = ['blue' if '@uwetech.com' in email else 'red' for email in G.nodes]

​

nx.draw(G, x, with_labels=True, node_size=500, font_size=14, font_color='black',

        node_color=node_colors, edge_color="grey", width=2, alpha=0.9)

​

plt.show()

may_emails = email_data_copy[(email_data_copy['datetime'].dt.month == 5) & (email_data_copy['datetime'].dt.year == 2022)]

​

#emails sent from 'usr-ezr@uwetech.com' to 'usr-xfr@uwetech.com'

selected_emails = may_emails[(may_emails['sender'] == 'usr-ezr@uwetech.com') & (may_emails['recipient'] == 'usr-xfr@uwetech.com')]

​

selected_emails